﻿<%@ WebHandler Language="C#" Class="ContainerCheckInfoLookBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;
using System.Collections;

/**
 * Description: 检验任务看板
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2023.2.28
 */

public class ContainerCheckInfoLookBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["action"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string action = jObject["action"].ToString();
                switch (action)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getExportExcel":
                        getExportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }


    //查询公用sql
    string getQuerySql(JObject jObject)
    {
        string sql = "";
        if (jObject["factorycode"].ToString() == "602") //热表厂
        {
            sql = @"
SELECT cci.productname,
       cci.productdescription,
       cci.productrevision,
       cci.containerno,
       cci.containername,
       cci.workflowstepname currentspec,
       csr.workflowstepname,
       cci.workflowname,
       cci.workflowrevision,
       '普通工序' spectype,
       csr.reportqty,csr.reportemployeename,csr.reportdate,csr.workcentername,
       NULL emDate,NULL emPerson,
       NULL cniDate,NULL cniPerson,
       NULL sriDate,NULL sriPerson,
       NULL dealDate,NULL dealPerson,
       NULL notes,NULL tochecker
FROM containersteprecord csr
LEFT JOIN containercurrentinfo cci ON cci.containerid = csr.containerid
WHERE cci.isinstore = 0 AND cci.containerstatus = 1 AND csr.reportdate IS NOT NULL AND NVL(csr.checkemployeeid,'0') = '0' ";
            //分厂
            if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
            {
                sql += string.Format(" AND cci.originalfactoryid = '{0}' ", jObject["factoryid"].ToString());
            }

            //零件图号
            if (jObject["productname"] != null && jObject["productname"].ToString() != "")
            {
                sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
            }

            //报工时间范围
            if (jObject["days"] != null && jObject["days"].ToString() != "")
            {
                sql += string.Format(" AND csr.reportdate > SYSDATE - '{0}'", jObject["days"].ToString());
            }

            //流水卡号
            if (jObject["containername"] != null && jObject["containername"].ToString() != "")
            {
                sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
            }

            //处理状态
            if (jObject["status"] != null && jObject["status"].ToString() != "")
            {
                if (jObject["status"].ToString() == "待检")
                {
                    sql += " AND 1=1 ";
                }
                else
                {
                    sql += " AND 1=0 ";
                }
            }

            //工序类型
            if (jObject["spectype"] != null && jObject["spectype"].ToString() != "")
            {
                string spectype = jObject["spectype"].ToString();
                if (spectype != "普通工序")
                {
                    sql += " AND 1=0";
                }
            }

            sql += " ORDER BY csr.reportdate";
        }
        else
        {
            sql = @"
SELECT cci.productname,
       cci.productdescription,
       cci.productrevision,
       cci.containerno,
       cci.containername,
       cci.workflowstepname currentspec,
       csr.workflowstepname,
       csr.workflowname,
       csr.workflowrevision,
       CASE WHEN NVL(s.lastcheck,0) = 0 THEN '普通工序'
            WHEN NVL(s.lastcheck,0) = 1 THEN '检验工序'
       END spectype, 
       csr.reportqty,csr.workcentername,csr.reportemployeename,
       csr.reportdate,em.createdate emDate,e.fullname emPerson,
       cni.createdate cniDate,e2.fullname cniPerson,sri.createdate sriDate,sri.checkemployee sriPerson,ccs.createdate dealDate,
       e3.fullname dealPerson,ccs.notes,ck.fullname tochecker
FROM containercurrentinfo cci
LEFT JOIN containersteprecord csr ON cci.containerid = csr.containerid
LEFT JOIN workflow wf ON wf.workflowid = csr.workflowid
LEFT JOIN spec s ON s.specid = csr.specid
LEFT JOIN EntrustMeasurement em ON em.containerid = cci.containerid --委托计量信息
LEFT JOIN containernonconforminginfo cni ON cni.containerid = cci.containerid --不合格品审理信息
LEFT JOIN StepReworkInfo sri ON sri.containerid = cci.containerid --返工信息
LEFT JOIN containerchecksuspending ccs ON ccs.containerid = cci.containerid AND csr.workflowstepid = ccs.workflowstepid AND ccs.isuse = 0 --待处理信息
LEFT JOIN employee e ON e.employeeid = em.entrustorid
LEFT JOIN employee e2 ON e2.employeeid = cni.employeeid
LEFT JOIN employee e3 ON e3.employeeid = ccs.createemployeeid
LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = csr.containersteprecordid--查询报表所选检验人 
LEFT JOIN employee ck ON ck.employeeid = csw.checkerid
WHERE cci.isinstore = 0 AND cci.containerstatus = 1 AND NVL(csr.checkemployeeid,'0') = '0' AND wf.factoryid = s.factoryid AND csr.reportdate IS NOT NULL ";
            //分厂
            if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
            {
                sql += string.Format(" AND cci.originalfactoryid = '{0}' ", jObject["factoryid"].ToString());
            }

            //零件图号
            if (jObject["productname"] != null && jObject["productname"].ToString() != "")
            {
                sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
            }

            //报工时间范围
            if (jObject["days"] != null && jObject["days"].ToString() != "")
            {
                sql += string.Format(" AND csr.reportdate > SYSDATE - '{0}'", jObject["days"].ToString());
            }

            //流水卡号
            if (jObject["containername"] != null && jObject["containername"].ToString() != "")
            {
                sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
            }

            //处理状态
            if (jObject["status"] != null && jObject["status"].ToString() != "")
            {
                if (jObject["status"].ToString() == "待检")
                {
                    sql += " AND NVL(NVL(cni.createdate,sri.createdate),ccs.createdate) IS NULL ";
                }
                else
                {
                    sql += " AND NVL(NVL(cni.createdate,sri.createdate),ccs.createdate) IS NOT NULL ";
                }
            }

            //工序类型
            if (jObject["spectype"] != null && jObject["spectype"].ToString() != "")
            {
                string spectype = jObject["spectype"].ToString();
                if (spectype == "普通工序")
                {
                    sql += " AND NVL(s.lastcheck,0) = 0";
                }
                else
                {
                    sql += " AND NVL(s.lastcheck,0) = 1";
                }
            }

            sql += " ORDER BY csr.reportdate";
        }
        return sql;
    }

    DataTable computeData(DataTable dt)
    {
        DataTable dtResult = dt;
        dtResult.Columns.Add("delayhours");//超期时间
        dtResult.Columns.Add("status");// 状态
        dtResult.Columns.Add("fullname");//检验员
        dtResult.Columns.Add("otherDalayHours");//其他超期时间

        foreach (DataRow dr in dtResult.Rows)
        {
            if (dr["emDate"].ToString() == "" && dr["cniDate"].ToString() == "" && dr["sriDate"].ToString() == "")
            {
                DateTime now = DateTime.Now;
                DateTime reportDate = Convert.ToDateTime(dr["reportdate"].ToString());
                dr["delayhours"] = Math.Round(now.Subtract(reportDate).TotalHours, 2);
            }
            if (dr["emDate"].ToString() != "")
            {
                DateTime emDate = Convert.ToDateTime(dr["emDate"].ToString());
                DateTime reportDate = Convert.ToDateTime(dr["reportdate"].ToString());
                dr["delayhours"] = Math.Round(emDate.Subtract(reportDate).TotalHours, 2);
            }
            if (dr["cniDate"].ToString() != "")
            {
                DateTime cniDate = Convert.ToDateTime(dr["cniDate"].ToString());
                DateTime reportDate = Convert.ToDateTime(dr["reportdate"].ToString());
                dr["delayhours"] = Math.Round(cniDate.Subtract(reportDate).TotalHours, 2);
            }
            if (dr["sriDate"].ToString() != "")
            {
                DateTime sriDate = Convert.ToDateTime(dr["sriDate"].ToString());
                DateTime reportDate = Convert.ToDateTime(dr["reportdate"].ToString());
                dr["delayhours"] = Math.Round(sriDate.Subtract(reportDate).TotalHours, 2);
                if (Convert.ToDecimal(dr["delayhours"]) < 0)
                {
                    dr["delayhours"] = "0";
                }
            }

            if (dr["emDate"].ToString() == "" && dr["cniDate"].ToString() == "" && dr["sriDate"].ToString() == "")
            {
                dr["status"] = "待检";
            }
            if (dr["emDate"].ToString() != "" && dr["cniDate"].ToString() == "" && dr["sriDate"].ToString() == "")
            {
                dr["status"] = "提交计量";
            }
            if (dr["emDate"].ToString() == "" && dr["cniDate"].ToString() != "" && dr["sriDate"].ToString() == "")
            {
                dr["status"] = "提交审理";
            }
            if (dr["emDate"].ToString() == "" && dr["cniDate"].ToString() == "" && dr["sriDate"].ToString() != "")
            {
                dr["status"] = "工人返修";
            }
            if (dr["emDate"].ToString() != "" && dr["cniDate"].ToString() != "" && dr["sriDate"].ToString() == "")
            {
                if (DateTime.Compare(Convert.ToDateTime(dr["emDate"].ToString()), Convert.ToDateTime(dr["cniDate"].ToString())) > 0)
                {
                    dr["status"] = "提交计量";
                }
                else
                {
                    dr["status"] = "提交审理";
                }
            }
            if (dr["emDate"].ToString() != "" && dr["cniDate"].ToString() == "" && dr["sriDate"].ToString() != "")
            {
                if (DateTime.Compare(Convert.ToDateTime(dr["emDate"].ToString()), Convert.ToDateTime(dr["sriDate"].ToString())) > 0)
                {
                    dr["status"] = "提交计量";
                }
                else
                {
                    dr["status"] = "工人返修";
                }
            }
            if (dr["emDate"].ToString() == "" && dr["cniDate"].ToString() != "" && dr["sriDate"].ToString() != "")
            {
                if (DateTime.Compare(Convert.ToDateTime(dr["cniDate"].ToString()), Convert.ToDateTime(dr["sriDate"].ToString())) > 0)
                {
                    dr["status"] = "提交审理";
                }
                else
                {
                    dr["status"] = "工人返修";
                }
            }
            if (dr["emDate"].ToString() != "" && dr["cniDate"].ToString() != "" && dr["sriDate"].ToString() != "")
            {
                DateTime emDate = Convert.ToDateTime(dr["emDate"].ToString());
                DateTime cniDate = Convert.ToDateTime(dr["cniDate"].ToString());
                DateTime sriDate = Convert.ToDateTime(dr["sriDate"].ToString());
                if (DateTime.Compare(emDate, cniDate) > 0)
                {
                    if (DateTime.Compare(emDate, sriDate) > 0)
                    {
                        dr["status"] = "提交计量";
                    }
                    else
                    {
                        if (DateTime.Compare(cniDate, sriDate) > 0)
                        {
                            dr["status"] = "提交审理";
                        }
                        else
                        {
                            dr["status"] = "工人返修";
                        }
                    }
                }
                else
                {
                    if (DateTime.Compare(cniDate, sriDate) > 0)
                    {
                        dr["status"] = "提交审理";
                    }
                    else
                    {
                        if (DateTime.Compare(emDate, sriDate) > 0)
                        {
                            dr["status"] = "提交计量";
                        }
                        else
                        {
                            dr["status"] = "工人返修";
                        }
                    }
                }
            }

            if (dr["emDate"].ToString() == "" && dr["cniDate"].ToString() == "" && dr["sriDate"].ToString() == "")
            {
                dr["fullname"] = "";
            }
            if (dr["emDate"].ToString() != "")
            {
                dr["fullname"] = dr["emPerson"].ToString();
            }
            if (dr["cniDate"].ToString() != "")
            {
                dr["fullname"] = dr["cniPerson"].ToString();
            }
            if (dr["sriDate"].ToString() != "")
            {
                dr["fullname"] = dr["sriPerson"].ToString();
            }

            if (dr["emDate"].ToString() == "" && dr["cniDate"].ToString() == "" && dr["sriDate"].ToString() == "")
            {
                dr["otherDalayHours"] = "0";
            }
            if (dr["emDate"].ToString() != "" && dr["cniDate"].ToString() == "" && dr["sriDate"].ToString() == "")
            {
                DateTime emDate = Convert.ToDateTime(dr["emDate"]);
                DateTime now = DateTime.Now;
                dr["otherDalayHours"] = Math.Round(now.Subtract(emDate).TotalHours, 2);
            }
            if (dr["emDate"].ToString() == "" && dr["cniDate"].ToString() != "" && dr["sriDate"].ToString() == "")
            {
                DateTime cniDate = Convert.ToDateTime(dr["cniDate"]);
                DateTime now = DateTime.Now;
                dr["otherDalayHours"] = Math.Round(now.Subtract(cniDate).TotalHours, 2);
            }
            if (dr["emDate"].ToString() == "" && dr["cniDate"].ToString() == "" && dr["sriDate"].ToString() != "")
            {
                DateTime sriDate = Convert.ToDateTime(dr["sriDate"]);
                DateTime now = DateTime.Now;
                dr["otherDalayHours"] = Math.Round(now.Subtract(sriDate).TotalHours, 2);
            }
            if (dr["emDate"].ToString() != "" && dr["cniDate"].ToString() != "" && dr["sriDate"].ToString() == "")
            {
                DateTime emDate = Convert.ToDateTime(dr["emDate"]);
                DateTime cniDate = Convert.ToDateTime(dr["cniDate"]);
                DateTime now = DateTime.Now;
                if (DateTime.Compare(emDate, cniDate) > 0)
                {
                    dr["otherDalayHours"] = Math.Round(emDate.Subtract(now).TotalHours, 2);
                }
                else
                {
                    dr["otherDalayHours"] = Math.Round(cniDate.Subtract(now).TotalHours, 2);
                }
            }
            if (dr["emDate"].ToString() != "" && dr["cniDate"].ToString() == "" && dr["sriDate"].ToString() != "")
            {
                DateTime emDate = Convert.ToDateTime(dr["emDate"]);
                DateTime sriDate = Convert.ToDateTime(dr["sriDate"]);
                DateTime now = DateTime.Now;
                if (DateTime.Compare(emDate, sriDate) > 0)
                {
                    dr["otherDalayHours"] = Math.Round(emDate.Subtract(now).TotalHours, 2);
                }
                else
                {
                    dr["otherDalayHours"] = Math.Round(sriDate.Subtract(now).TotalHours, 2);
                }
            }
            if (dr["emDate"].ToString() == "" && dr["cniDate"].ToString() != "" && dr["sriDate"].ToString() != "")
            {
                DateTime cniDate = Convert.ToDateTime(dr["cniDate"]);
                DateTime sriDate = Convert.ToDateTime(dr["sriDate"]);
                DateTime now = DateTime.Now;
                if (DateTime.Compare(cniDate, sriDate) > 0)
                {
                    dr["otherDalayHours"] = Math.Round(cniDate.Subtract(now).TotalHours, 2);
                }
                else
                {
                    dr["otherDalayHours"] = Math.Round(sriDate.Subtract(now).TotalHours, 2);
                }
            }
            if (dr["emDate"].ToString() != "" && dr["cniDate"].ToString() != "" && dr["sriDate"].ToString() != "")
            {
                DateTime emDate = Convert.ToDateTime(dr["emDate"]);
                DateTime cniDate = Convert.ToDateTime(dr["cniDate"]);
                DateTime sriDate = Convert.ToDateTime(dr["sriDate"]);
                DateTime now = DateTime.Now;
                if (DateTime.Compare(emDate, cniDate) > 0)
                {
                    if (DateTime.Compare(emDate, sriDate) > 0)
                    {
                        dr["otherDalayHours"] = Math.Round(emDate.Subtract(now).TotalHours, 2);
                    }
                    else
                    {
                        if (DateTime.Compare(cniDate, sriDate) > 0)
                        {
                            dr["otherDalayHours"] = Math.Round(cniDate.Subtract(now).TotalHours, 2);
                        }
                        else
                        {
                            dr["otherDalayHours"] = Math.Round(sriDate.Subtract(now).TotalHours, 2);
                        }
                    }

                }
                else
                {
                    if (DateTime.Compare(cniDate, sriDate) > 0)
                    {
                        dr["otherDalayHours"] = Math.Round(cniDate.Subtract(now).TotalHours, 2);
                    }
                    else
                    {
                        if (DateTime.Compare(emDate, sriDate) > 0)
                        {
                            dr["otherDalayHours"] = Math.Round(emDate.Subtract(now).TotalHours, 2);
                        }
                        else
                        {
                            dr["otherDalayHours"] = Math.Round(sriDate.Subtract(now).TotalHours, 2);
                        }
                    }
                }
            }

        }

        foreach (DataRow dr in dtResult.Rows)
        {
            if (dr["status"].ToString() == "待检")
            {
                if (dr["dealDate"].ToString() != "")
                {
                    dr["status"] = "待处理";

                    DateTime dealDate = Convert.ToDateTime(dr["dealDate"]);
                    DateTime now = DateTime.Now;
                    dr["otherDalayHours"] = Math.Round(now.Subtract(dealDate).TotalHours, 2);
                }
            }
        }

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        return dtResult;
    }

    //查询待检验信息 add zhunan 2017-10-12
    Result getData(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dtResult = computeData(dto.DBTable);
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void getExportExcel(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        DataTable dtResult = computeData(dal.GetDataTable(sql));
        foreach (DataRow dr in dtResult.Rows)
        {
            dr["delayhours"] = dr["delayhours"] + "小时";
            dr["otherdalayhours"] = dr["otherdalayhours"] + "小时";
        }
        dtResult.Columns.Remove("emdate");
        dtResult.Columns.Remove("emperson");
        dtResult.Columns.Remove("cnidate");
        dtResult.Columns.Remove("cniperson");
        dtResult.Columns.Remove("sridate");
        dtResult.Columns.Remove("sriperson");
        dtResult.Columns.Remove("dealdate");
        dtResult.Columns.Remove("dealperson");
        dtResult.Columns.Remove("reportemployeename");
        dtResult.Columns["productrevision"].Caption = "零件版本";
        dtResult.Columns["containerno"].Caption = "批次号";
        dtResult.Columns["workflowname"].Caption = "工艺路线";
        dtResult.Columns["workflowrevision"].Caption = "工艺版本";
        dtResult.Columns["spectype"].Caption = "工序类型";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["workflowstepname"].Caption = "工序";
        dtResult.Columns["reportqty"].Caption = "数量";
        dtResult.Columns["workcentername"].Caption = "工区";
        dtResult.Columns["delayhours"].Caption = "等待时间";
        dtResult.Columns["reportdate"].Caption = "工人报工时间";
        dtResult.Columns["status"].Caption = "状态";
        dtResult.Columns["fullname"].Caption = "检验员";
        dtResult.Columns["otherdalayhours"].Caption = "其他等待时间";
        dtResult.Columns["notes"].Caption = "备注";
        dtResult.Columns["tochecker"].Caption = "待检人";
        dtResult.Columns["currentspec"].Caption = "当前工序";
        ExportHelper.ExportExcel(dtResult, jObject["factoryname"].ToString() + "检验任务看板");
    }
}